package org.csu.persistence.IMPL;

import org.csu.db.DBUtils;
import org.csu.domain.Item;
import org.csu.domain.Product;
import org.csu.persistence.ItemDAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class ItemDAOImpl implements ItemDAO {
    private static final String SELECT_ALL_BY_ITEMID="SELECT * FROM item WHERE itemid = ?";
    private static final String SELECT_ALL_BY_PRODUCTID="SELECT * FROM item WHERE productid = ?";
    private static final String getInventoryQuantityString = "SELECT QTY AS QUANTITY FROM INVENTORY WHERE ITEMID = ?";
    private static final String updateInventoryQuantityString = "UPDATE INVENTORY SET QTY = QTY - ? WHERE ITEMID = ?";

    @Override
    public void updateInventoryQuantity(Map<String, Object> param) {
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement pStatement = connection
                    .prepareStatement(updateInventoryQuantityString);
            String itemId = param.keySet().iterator().next();
            Integer increment = (Integer) param.get(itemId);
            pStatement.setInt(1, increment.intValue());
            pStatement.setString(2, itemId);
            pStatement.executeUpdate();
            DBUtils.closePreparedStatement(pStatement);
            DBUtils.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    @Override
    public int getInventoryQuantity(String itemId) {
        int result = -1;
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(getInventoryQuantityString);
            preparedStatement.setString(1,itemId);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                result = resultSet.getInt(1);
            }
            resultSet.close();
            preparedStatement.close();
            connection.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public List<Item> getItemListByProduct(String productId) {
        List<Item> itemList = new ArrayList<Item>();
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(SELECT_ALL_BY_PRODUCTID);
            preparedStatement.setString(1,productId);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                Item item = new Item();
                item.setItemId(resultSet.getString(1));
                item.setProductId(resultSet.getString(2));
                item.setListPrice(resultSet.getBigDecimal(3));
                item.setUnitCost(resultSet.getBigDecimal(4));
                item.setSupplierId(resultSet.getInt(5));
                item.setStatus(resultSet.getString(6));
                item.setAttribute1(resultSet.getString(7));
                item.setAttribute2(resultSet.getString(8));
                item.setAttribute3(resultSet.getString(9));
                item.setAttribute4(resultSet.getString(10));
                item.setAttribute5(resultSet.getString(11));
                itemList.add(item);
            }
            resultSet.close();
            preparedStatement.close();
            connection.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        return itemList;
    }


    @Override
    public Item getItem(String itemId) {
        Item item = null;
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(SELECT_ALL_BY_ITEMID);
            preparedStatement.setString(1,itemId);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                item = new Item();
                item.setItemId(resultSet.getString(1));
                item.setProductId(resultSet.getString(2));
                item.setListPrice(resultSet.getBigDecimal(3));
                item.setUnitCost(resultSet.getBigDecimal(4));
                item.setSupplierId(resultSet.getInt(5));
                item.setStatus(resultSet.getString(6));
                item.setAttribute1(resultSet.getString(7));
                item.setAttribute2(resultSet.getString(8));
                item.setAttribute3(resultSet.getString(9));
                item.setAttribute4(resultSet.getString(10));
                item.setAttribute5(resultSet.getString(11));
            }
            resultSet.close();
            preparedStatement.close();
            connection.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        return item;
    }
}
